

/**
 * @function sqlOptions 将对象转换成sql语句
 *
 * @param {{
 *     name: string,
 *     select: string,
 *     method: 'save'|"list"|"delete"|"update"|'total'|'procedure'|'call' ,
 *     data: Object | Array,
 *     if: "OR"|"AND",
 *     current: number,
 *     pageSize: number,
 *     key: string,
 *     procedure: "brand",
 *     value: string,
 *     like: boolean
 * }} option
 * name: 表名
 * select: 列名
 * method: save添加, list列表查询, delete删除, update更新, total查询表的总数
 * if 并且查询 或者查询
 * current 列表查询的第几页
 * pageSize 列表查询的页码
 * key: 更新时的表key
 *
 * @return string
 */
const sqlOptions = (option) => {
    const {method, name, select, procedure, value} = option
    const type = method || 'list'
    switch (type) {
        case "list":
            return list(option);
        case "save":
            return save(option)
        case "delete":
            return del(option)
        case "update":
            return update(option)
        case "total":
            return `SELECT COUNT(${select||'*'}) FROM ${name}`
        case "procedure":
            switch (procedure) {
                case "brand":
                    return `CALL delete_rows_brand_product_recommend('${value}');`
            }
            break
        case 'call':
            return `CALL ${procedure}('${value}')`

    }
}

/**
 * @param {{
 *     name: string,
 *     select: string,
 *     method: 'list',
 *     data: Object,
 *     if: "OR"|"AND",
 *     current: number,
 *     pageSize: number,
 *     key: string,
 *     like: boolean
 * }} option
 */
function list(option) {
    const {name, select, data, current, pageSize, key: keys, like} = option
    const {key, tableKey} = data
    const arr = []
    const list = []
    const term = option.if || 'OR'
    let str;
    let LIMIT;
    let OFFSET;
    // 数组查询
    if ('key' in data) {
        for (let i = 0; i < data.list.length; i++) {
            if (i === data.list.length - 1) {
                arr.push("SELECT '"+ list[i][key] +"' AS "+ key +" ")
            }else {
                arr.push("SELECT '"+ list[i][key] +"' AS "+ key +" UNION")
            }
        }
        if (current) {
            OFFSET = `OFFSET ${(current - 1) * 10}`
        }else {
            OFFSET = ''
        }
        if (pageSize) {
            LIMIT = `LIMIT ${pageSize}`
        }else {
            LIMIT = ""
        }
        return `SELECT ${select || '*'} FROM ${name} WHERE ${tableKey} IN (${arr.join(' ')}) ${LIMIT} ${OFFSET}`
    }
    // 非数组查询
    for (const key in data) {
        if (data[key]) {
            if (keys === key && like){
                list.push(`${key} LIKE '%${data[key]}%'`)
            }else {
                list.push(`${key} = '${data[key]}'`)
            }

        }
    }
    if (list.length) {
        str = `WHERE ${list.join(' '+term+' ')}`
    }else {
        str = ''
    }
    if (current) {
        OFFSET = `OFFSET ${(current - 1) * 10}`
    }else {
        OFFSET = ''
    }
    if (pageSize) {
        LIMIT = `LIMIT ${pageSize}`
    }else {
        LIMIT = ""
    }
    return `SELECT ${select || '*'} FROM ${name} ${str} ${LIMIT} ${OFFSET}`
}

/**
 * @param {{
 *     name: string,
 *     method: 'save',
 *     data: Object | Array,
 * }} option
 */
function save (option) {
    const {name, data} = option
    // 批量添加
    if (Array.isArray(data)) {
        const keys = Object.keys(data[0])
        const values = []
        for (const item of data) {
            let keyArr = []
            for (const key in item) {
                keyArr.push(`'${item[key]}'`)
            }
            values.push(`(${keyArr.join(',')})`)
        }
        return `INSERT INTO ${name} (${keys.join(',')}) VALUES ${values.join(', ')}`
    }
    // 单独添加
    const keys = Object.keys(data).join(',')
    const values = Object.values(data)
    let sql = `INSERT INTO ${name} (${keys}) VALUES (`
    for (const item of values) {
        sql += `'${item}',`
    }
    // 删除最后的逗号
    let newStr = sql.substring(0, sql.length - 1);
    return newStr + ')'
}

/**
 *
 * @param {{
 *      if: "OR"|"AND",
 *      name: string,
 *      data: Object,
 *      key: string
 * }} option
 */
function update (option) {
    const {name, data, key} = option
    const arr = []
    for (const keys in data) {
        arr.push(`${keys} = '${data[keys]}'`)
    }
    const list = []
    for (const keys in key) {
        list.push(`${keys} = '${key[keys]}'`)
    }
    const term = option.if || 'OR'
    return `UPDATE ${name} SET ${arr.join(',')} WHERE ${list.join(' '+ term +' ')}`
}

/**
 *
 * @param {{
 *      if: "OR"|"AND",
 *      name: string,
 *      data: Array | Object,
 * }} option
 */
function del (option) {
    const {name, data} = option
    const term = option.if || 'OR'
    let str;
    const arr = [];
    if (Array.isArray(data)){
        for (const item of data) {
            for (const key in item) {
                arr.push(`${key} = '${item[key]}'`)
            }
        }
    }else {
        for (const key in data) {
            arr.push(`${key} = '${data[key]}'`)
        }
    }
    if (arr.length){
        str = `WHERE ${arr.join(' '+term+' ')}`
    }else {
        str = ''
    }
    return `DELETE FROM ${name} ${str}`
}

module.exports = sqlOptions